Exploratory data analysis example - 1C Company

This notebook shows some exploratory data exploration of retail sales data in the context of predicting future sales numbers of items at different retail outlets.

This data was provided by the Russian software publisher and retailer 1C Company, for a Kaggle competition in which the challenge is to predict monthly sales for specific products in specific shops.

Table of contents

Load, check and clean data

Import python packages

The first thing to do is import a standard set of Python packages for data manipulation and analysis. Pandas and NumPy are used to store and manipulate tabular data, Matplotlib and Seaborn are used for visualization, and a few standard Python libraries are imported also.

Load data

First we'll list the files in the data directory.

We'll load all the files into the workspace as pandas dataframes with the same variable names as the csv files, other than "sales_train", which will be renamed to "train" to save time typing. Additionally, two Russian language text columns are replaced with English translated versions>

Show table contents

We have a quick look at first few lines of each of the tables to get a basic idea of their contents.

Merge tables

It looks like the training data has been structured as normalized tables for efficiency. For convenience we can merge the training data into a single table by joining them on their shared fields.

Check and convert datatypes

We list the datatypes of the columns to check if they could be improved.

The date field is formatted as a string, we can convert that to the datetime dtype to enable extra datetime features such as grouping by weeks or months.

The other columns have an appropriate datatype but could be downcasted to a lower-precision datatype to save memory without any loss in accuracy. This is done here with a custom downcasting function that downcasts integer columns to the smallest type that can represent all column values, and converts float-type columns to the float32 type.

Check for missing values

Now we have prepared the dataframe we can check it for missing values.

There aren't any, good!

First look and outlier removal

The full dataset is assembled, so we can look at the first few rows to get an idea of its contents.

It looks like the rows are individual sales counts for specific shop-item combinations on specific days, with the following columns:

We now use the pandas "describe" method to get a summary of the distribution of values in each numerical column. We'll round values to the nearest integer for clarity and append the number of unique values for each column.

From this we can see:

The very highest-valued entries of the item_price and item_cnt_month are so few in number they can be looked at individually. Judging by a quick inspection, the very highest valued entries are custom orders for large numbers of items and are probably best removed.

We remove outliers and negative values by retaining only rows with item_price and item_cnt_day values within specific thresholds.

Checking the validity of shop_id and shop_name column values

The training data contains multiple shop_ids, not all of which are in the test set. To get an overview we can create a figure which plots total sales for each shop by month and displays the shop names in Russian and English. A table with a list of translated shop names is used for this.

Correct shop labels and remove data from unusual shops

A closer look at these plots finds several data cleaning issues, such as:

We will merge the duplicate shops and remove all data from shops not in the test month, for simplicity.

(A similar check of item categories finds no data quality issues)

Remove duplicate items

Finally, we'll check for remaining duplicate entries in the training data.

There are only 5 duplicate entries, but the fact that 4 of them are for the same product suggests that they are errors, so we might as well drop them.

Exploration of trends and distributions in the data

We can look at the the distributions of target and other features, and look for interesting relationships between features. The aim of this exploration should be to find patterns in the data that could help predict the target value, and identify the types of model that are appropriate for the prediction task.

Creating a model training dataset by aggregating the data by month

The challenge is to predict monthly sales totals, so we should create a model training set that reproduces the test format by summing sales for each month.

The format of the test items is a list of all possible combinations of shops and items for shops and items that recorded at least one sale in the test month, i.e. the Cartesian product of these shops and items. We recreate this by summing the sales for the Cartesian product of active shops and items sold in each month.

As before, we merge the items, categories and shops tables so these can be used as predictive features.

We show the head of the table to check it looks ok.

Distribution of the target feature

We plot an initial histogram of the target item_cnt_month feature, with a smoothed distribution estimate.

The distribution clearly has a very large peak close to zero. Creating sales counts for all possible combinations of shop and item each month might lead to lots of entries with zero sales, we should check what proportion of item counts are now zero.

About 85% of target values are zero, which will clearly dominate any distribution plots. To get an idea of the distribution of non-zero values, we plot the distribution again with zero values removed.

Even with zeros removed, the target is very bottom-heavy, with only around 5% of values above 1, although a small number of items sell much more than this.

The skewness of the distribution makes linear models unsuitable for predicting future sales of items, as assumptions for linear models will not be met. Instead, non-linear models such as decision trees or k-nearest neighbor models would be more suitable.

Item price distribution

We can also plot the distribution of items prices. For this, we take the mean price of the item in months in which it was sold.

Prices are also highly skewed towards zero. We can display the distribution more clearly by using a log scale.

Here we can see that the price distribution is approximately lognormal with a peak slightly below 10.

Joint distribution of item prices and sales

Finally, we can also plot the joint distribution of monthly items sales and mean prices, again using log scales for clarity.

This reveals no strong overall associations between price and sales, although associations may exist in subgroups of the data.

Outliers are also apparent.

Plotting total sales counts per month shows clear downwards and seasonal trends. Mean sales per item, however, shows a less pronounced downward trend.

Mean sales per item can also be decomposed into seasonal and continuous trends using the statsmodels package. This show a clear yearly seasonal trend (particularly a peak around the winter holidays) and an overall downwards trend that can be assumed to related to the rise of internet and digital-only sales.

The overall sales trend is clearly downwards, but there are differences at the item category level. Compare the following trend and seasonal decomposition plots for games for the PS3 and PS4, which show falling and rising trends.

In any case, including a seasonal variable (i.e. a month feature) should help the prediction model capture seasonal trends.

Sales by item category

Each item is assigned one of 80+ categories which identify what kind of product it is and what format it is for. Information about these categories could be predictive because different types of item are likely to sell in different amounts.

First we plot mean sales and revenue per item in each category across all shops for the last year of sales data and sort by descending magnitude.

Most obviously this plot shows that items in one particular category - "Gifts - bags, albums, mousepads" - have a much higher sales volume than items in other categories, but comparitively little revenue. This category contains few items and denotes low cost items such as promotional bags and mousemats.

Economic importance may be better represented by plotting summed rather than mean values in each category. Doing so shows that movies and games are the highest-selling categories overall, with PS4 games accounting for the most revenue. As well as being predictive in itself, this information could be useful for deciding what categories to prioritize when building predictive models or allocating promotional resources.

Sales by shop category

Summed sales can similarly be plotted when data is grouped according to shop.

As with categories, this shows that a small number of shops, paricularly those in Moscow, sell much more items overall than others. All else being equal, an item sold in these shops can be predicted to sell in greater quantities.

Shop - category sales profile and decomposition of sales

Some shops sell more than others, and items in some item categories sell more than others, but are there differences in the relative quantities of items in each category that shops sell?

The individual summed sales per category profile of each shop can be created and is plotted here as a heatmap.

PCA decomposition and clustering of shops

The vertical stripes in the heatmap indicate shops that differ from the mean in some way, but high-dimensional data like this can be difficult to understand without some kind of summary.

Principle component analysis (PCA) lets us decompose high dimensional data into a low dimensional representation that makes it easier to get an overview of patterns in the data. Although the values in the shop-item category sales data do not have the normal distribution assumed by the PCA algorithm, this can be partially corrected by log transforming the data.

Below are the results of the PCA-decomposition of the shop-item_category sales profiles using the implementation in scikit-learn. The top-left plot shows the proportion of the overall variance explained by each component, while the remaining 3 plots show the shops plotted on scatter plots by their scores on the first 6 principle components.

The explained variance plot shows that around 75% of the differences between the shops can be explained by two linear components.

Plotting the shops according to their scores on the first two components shows that the shop with shop_id 55 is particularly unlike the other shops, while plotting the shops according to their other components reveals that shop 12 is also an outlier. This makes intuitive sense, as these are internet or virtual shops rather than physical stores.

Insight into what these components correspond to can be gained by plotting the components that map the component scores to the original data. We do this here for the two largest components, and sort the the component mappings for clarity.

Looking at component 1, we see that this is mostly highly weighted on "digital" categories, i.e. non-physical online downloads, which fits with the internet store 55 having a very high score in this dimension. Further components reveal other dimensions in the differences between shops, such as the sales of music and audiobook products being correlated.

Sales as a function of item age

The age of items when they are sold can be approximately calculated by subtracting from the sale date the first date or month on which they were sold.

Total monthly item sales as a function of item age is plotted below for all items, and separately for items in two representative categories.

Plotting total monthly item sales as a function of their ages shows that items tend to sell most when they are new and then decline to a plateau about 1 year later. The slightly lower sales in the first compared to the second month is attributable to items not always being available for the whole first month.

It is also evident that this trend for items to sell most shortly after their release is more evident for some categories, such as movies, compared to others.

Predicting sales from historical sales

Even when taking the decline of sales volume over time into account, it seems likely that products that sell well in one month are likely to also sell well in the following month. A column can be created which contains the sales figures from the previous month for the same shop-item combination.

We can create a regression plot of sales counts as a function of previous months sales, for a sample items which are at least a month old. We use log scales on the axes for clarity and plot the estimate of the central tendency (mean) of item_cnt_month.

Moving average of previous sales.

Single shop-item sales in individual months are mostly low-valued and tend to be noisy. To reduce this noise, windowing methods can be used to calculate a historical mean of sales as a weighted sum of the sales from multiple previous months. An example showing multiple types of window is shown below.

The problem of new items

Historical sales figures are obviously not directly available for items in their first month of availability, so information about sales of similar items must be used instead. Ideally, multiple measures of similarity should be found and tested.

The item_category_id field can be used to group similar items, and the shop_id field can be used to find items sold in the same shop. These can be combined with the item_age engineered feature to calculate mean first-month sales counts for products grouped by item category or shop. This is plotted below for the last available year of sales data.

As before for items of all ages, the mean new item sales count can also be calculated for each item category - shop combination, plotted below as a heat map.

Using text features to find similar items

In addition to the information contained in the item categories and shop identities, all items have an associated item_name text feature, which contains a short description of the item that often includes things such as its title, format (e.g. PS4 or PS3) and language. Information can be potentially be extracted from this text string to group similar items.

To aid extraction of information from text it is useful to clean the text of irrelevant special characters and punctuation, excess blank spaces, low-information words such as "the", removing diacritics such as accents over letters, and converting all text to lowercase. If necessary this can be performed by regular expression operations, as demonstrated here:

Word and n-gram features

One way that the item_name field could be used is by extracting individual words or n-grams (groups of sequential words) and treating them as individual binary features. Doing this creates a very large number of features so some kind of filtering is necessary, such as specifying minimum numbers of occurences of a word feature, or setting a threshold on a measure of relevance to the target feature, such as correlation.

Below is an example of the 1 and 2-ngrams producted from a single item_name text string.

Grouping similar items with similar names

Items with different item_ids are often related to each other, such as being different versions of the same video game or movie, and so are likely to have related sales figures. This can be taken advantage of by grouping similar items together based on their item_names.

The Python package TheFuzz implements fuzzy string matching to measure the similarity of sequences of words. This similarity measure can be used to group related items together into a single category. This "item name group" feature can then be used as other categorical features.

Below is a short extract of the list of items with their associated name group category designations, showing related items assigned to the same category value.

Mean encoding and none-stationarity of category sales

Categorical features such as name groups and shop ids have useful predictive information, but the relationship between category values and the target variable is not consistent over the training data, as items, item categories and shops increase or (more often) decline in popularity over time, as can be seen when plotting mean sales of music item categories.

Theoretically, a model could learn the interactions between individual category values and time periods, but for categorical features with high numbers of values, such as item identities and name groups, this could require very complex models and cause problems with overfitting.

To save the predictive model having to learn the individual relationships between individual categories and specific time periods, a useful solution is to turn categorical features into numerical features by reencoding each value of the category as the mean of the target variable for training items in the same category, for some time window. As with individual items, different time windows can be used.

Below are example windowed mean encodings for 3 values of the item category name feature using 3 different temporal windows.

Model fitting and fitted model exploration

The next step after exploratory data analysis should be the creation of a predictive model. Predictive models are obviously useful for planning actions, but a fitted model can also be used for further data exploration by extracting how the model relates features to the final prediction.

Model explanations with SHAP

Here we use the powerful model explanation package SHAP (SHapley Additive exPlanations) to gain additional insights about predictors of item sales. The model explained by SHAP is a LightGBM regressor, a gradient boosted decision tree model.

First a pre-prepared training frame is loaded and used to train the LightGBM regressor.

A SHAP explainer object is attached to the fitted model and used to generate SHAP values, which quantify the effect of each of the features on each prediction.

Feature importance ranking with beeswarm plots

The beeswarm plots below show a summary plot of the effect of each features on each prediction, and ranks the features by overall importance. Each prediction is represented by a point next to each feature name, with the position of the point on the left-right axis indicating the increase or decrease over the baseline prediction caused by the feature value, and the color of the point indicating the value of the feature. For example, a red marker towards the right hand side of the plot indicates that the feature had a high value and caused an increase in the prediction.

Here, the top feature is _shop_id_item_id_item_cnt_month_mean_ewm_hl1, which is the exponential moving average of previous sales for the same shop-item combination, with the expected relationship of higher previous sales predicting higher future sales. The fact that the model chose this as the most predictive feature confirms the predictive power of the moving average.

The second most predictive feature, _shop_id_item_category_id_new_item_item_cnt_month_mean_rolling_mean_win12 (the name is automatically generated) is a feature that contains the 12-month mean of sales of items with the same category in the same shop, restricted to either new (i.e. first month) items or non-new items depending on whether the item to be predicted is new or not. This is likely to be particularly valuable for predicting sales of new items, a hypothesis which can be explored with further visualizations.

Taking a closer look at feature effects with dependence plots

A clearer view of the relationships between the target feature and one or more predictors can be gained with dependence plots.

The following plot shows the relationship between the exponential moving average shop-item sales feature and predictions, showing an approximately linear relationship with increasing variance as feature values increase.

A dependence plot can also vizualize interactions between two predictor features by varying point color according to a second variable. Below is plotted the shop-item_category-new_item 12 month windowed mean, with new items coloured red and non-new items coloured blue. We hypothesized that this feature would be particularly valuable for new items, which is confirmed by the steeper slope of the line that can be traced through the red points in the graph.

Automatic detection of feature interactions

SHAP can also automatically calculate which other feature had the greatest interaction with a given feature when generating predictions. The dependence plot below shows the main effect of the "first_item_sale_days" feature, which gives the number of days between the first recorded sale of an item and the first day of the prediction month. This feature shows a main effect of predicting higher sales for newer items, which decreases over time. SHAP identifies as the main interaction feature _shop_cluster_item_id_item_cnt_month_mean_lag1, a feature which contains the mean sales of the same item in similar shops, plotting higher values of this feature in red. Looking at the plot, it can be observed that red points tend to be higher than blue points at low values of the main x-axis feature, and lower at higher values of the main feature, indicating that the main trend of decreasing sales with item age is more pronounced for high-selling items.